#region Copyright (c) 2006-2012 Widgetsphere LLC, All Rights Reserved
//--------------------------------------------------------------------- *
//                          Widgetsphere  LLC                           *
//             Copyright (c) 2006-2012 All Rights reserved              *
//                                                                      *
//                                                                      *
//This file and its contents are protected by United States and         *
//International copyright laws.  Unauthorized reproduction and/or       *
//distribution of all or any portion of the code contained herein       *
//is strictly prohibited and will result in severe civil and criminal   *
//penalties.  Any violations of this copyright will be prosecuted       *
//to the fullest extent possible under law.                             *
//                                                                      *
//THE SOURCE CODE CONTAINED HEREIN AND IN RELATED FILES IS PROVIDED     *
//TO THE REGISTERED DEVELOPER FOR THE PURPOSES OF EDUCATION AND         *
//TROUBLESHOOTING. UNDER NO CIRCUMSTANCES MAY ANY PORTION OF THE SOURCE *
//CODE BE DISTRIBUTED, DISCLOSED OR OTHERWISE MADE AVAILABLE TO ANY     *
//THIRD PARTY WITHOUT THE EXPRESS WRITTEN CONSENT OF WIDGETSPHERE LLC   *
//                                                                      *
//UNDER NO CIRCUMSTANCES MAY THE SOURCE CODE BE USED IN WHOLE OR IN     *
//PART, AS THE BASIS FOR CREATING A PRODUCT THAT PROVIDES THE SAME, OR  *
//SUBSTANTIALLY THE SAME, FUNCTIONALITY AS ANY WIDGETSPHERE PRODUCT.    *
//                                                                      *
//THE REGISTERED DEVELOPER ACKNOWLEDGES THAT THIS SOURCE CODE           *
//CONTAINS VALUABLE AND PROPRIETARY TRADE SECRETS OF WIDGETSPHERE,      *
//INC.  THE REGISTERED DEVELOPER AGREES TO EXPEND EVERY EFFORT TO       *
//INSURE ITS CONFIDENTIALITY.                                           *
//                                                                      *
//THE END USER LICENSE AGREEMENT (EULA) ACCOMPANYING THE PRODUCT        *
//PERMITS THE REGISTERED DEVELOPER TO REDISTRIBUTE THE PRODUCT IN       *
//EXECUTABLE FORM ONLY IN SUPPORT OF APPLICATIONS WRITTEN USING         *
//THE PRODUCT.  IT DOES NOT PROVIDE ANY RIGHTS REGARDING THE            *
//SOURCE CODE CONTAINED HEREIN.                                         *
//                                                                      *
//THIS COPYRIGHT NOTICE MAY NOT BE REMOVED FROM THIS FILE.              *
//--------------------------------------------------------------------- *
#endregion
using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using Widgetsphere.Generator.Common.Util;
using Widgetsphere.Generator.Models;
using Widgetsphere.Generator.Common.GeneratorFramework;

namespace Widgetsphere.Generator.Installer.ProjectItemGenerators.SQLStoredProcedureAll
{
	internal class SQLSelectBusinessObjectByForeignKeyTemplate : ISQLGenerate
	{
		private ModelRoot _model;
		private Relation _currentRelation;
		private Table _childTable;
		private Table _parentTable;

		#region Constructors
		public SQLSelectBusinessObjectByForeignKeyTemplate(ModelRoot model, Relation currentRelation, Table realTable)
		{
			_model = model;
			_currentRelation = currentRelation;
			_childTable = (Table)_currentRelation.ChildTableRef.Object;
			if (realTable.IsInheritedFrom(_childTable)) _childTable = realTable;
			_parentTable = (Table)_currentRelation.ParentTableRef.Object;
		}
		#endregion

		#region GenerateContent

		public void GenerateContent(StringBuilder sb)
		{
			if (_model.Database.AllowZeroTouch) return;
			try
			{
				this.AppendFullTemplate(sb);
			}
			catch (Exception ex)
			{
				throw;
			}
		}

		#endregion

		private void AppendFullTemplate(StringBuilder sb)
		{
			List<Table> allTables = new List<Table>(_parentTable.GetTablesInheritedFromHierarchy());
			allTables.Add(_parentTable);
			foreach (Table table in allTables.Where(x => x.Generated))
			{
				this.CreateProcedure(sb, table);
			}

		}

		private void CreateProcedure(StringBuilder sb, Table parentTable)
		{
			try
			{
				var storedProcName = "[" + _childTable.GetSQLSchema() + "].[" + GetStoredProcedureName(parentTable) + "]";
				sb.AppendLine("if exists (select * from dbo.sysobjects where id = object_id(N'" + storedProcName + "') and OBJECTPROPERTY(id, N'IsProcedure') = 1)");
				sb.AppendLine("	drop procedure " + storedProcName);
				sb.AppendLine("GO");
				sb.AppendLine();
				sb.AppendLine("SET QUOTED_IDENTIFIER ON ");
				sb.AppendLine("GO");
				sb.AppendLine("SET ANSI_NULLS ON");
				sb.AppendLine("GO");
				sb.AppendLine();

				if (_childTable.SelfReference && parentTable == _childTable)
				{
					sb.AppendLine("CREATE PROCEDURE " + storedProcName);
					sb.AppendLine("(");
					sb.AppendLine("	@xml xml,");
					sb.AppendLine("	@direction char(4),");
					sb.AppendLine("	@levels int");
					sb.AppendLine(")");
					sb.AppendLine("AS");
					sb.AppendLine();
					sb.AppendLine("DECLARE @newItemCount int");
					sb.AppendLine("DECLARE @count int");
					sb.AppendLine("DECLARE @index int");
					sb.AppendLine();
					sb.Append("CREATE TABLE #TmpIds (");

					int ii = 0;
					foreach (var columnRelationship in _childTable.SelfReferenceRelation.ColumnRelationships.ToList())
					{
						var parentColumn = columnRelationship.ParentColumn;
						var childColumn = columnRelationship.ChildColumn;
						sb.Append("[" + parentColumn.DatabaseName + "] " + parentColumn.DatabaseType);
						if (ii < _childTable.SelfReferenceRelation.ColumnRelationships.Count - 1) sb.Append(", ");
						ii++;
					}
					sb.AppendLine(")");
					sb.Append("INSERT INTO #TmpIds(");

					ii = 0;
					foreach (var columnRelationship in _childTable.SelfReferenceRelation.ColumnRelationships.ToList())
					{
						var parentColumn = columnRelationship.ParentColumn;
						var childColumn = columnRelationship.ChildColumn;
						sb.Append("[" + parentColumn.DatabaseName + "]");
						if (ii < _childTable.SelfReferenceRelation.ColumnRelationships.Count - 1) sb.Append(", ");
						ii++;
					}
					sb.AppendLine(")");

					sb.AppendLine("SELECT");

					ii = 0;
					foreach (var columnRelationship in _childTable.SelfReferenceRelation.ColumnRelationships.ToList())
					{
						var parentColumn = columnRelationship.ParentColumn;
						var childColumn = columnRelationship.ChildColumn;
						sb.Append("	[" + _childTable.DatabaseName + "].[" + childColumn.DatabaseName + "]"); //QQQ
						if (ii < _childTable.SelfReferenceRelation.ColumnRelationships.Count - 1) sb.AppendLine(", ");
						ii++;
					}
					sb.AppendLine();

					sb.AppendLine("FROM ");
					sb.AppendLine(_childTable.GetFullHierarchyTableJoin());
					sb.AppendLine("WHERE");
					sb.AppendLine("	" + BuildWhereStatement(_currentRelation, parentTable));
					sb.AppendLine();

					sb.AppendLine("IF (@direction = 'NONE')");
					sb.AppendLine("BEGIN");

					sb.AppendLine("SELECT");
					sb.Append(Globals.BuildSelectList(_childTable, _model, true));
					sb.AppendLine("FROM ");
					sb.AppendLine(_childTable.GetFullHierarchyTableJoin());
					sb.AppendLine("WHERE");
					sb.AppendLine("	" + BuildWhereStatement(_currentRelation, parentTable));

					sb.AppendLine("END");
					sb.AppendLine("ELSE");
					sb.AppendLine("BEGIN");
					sb.AppendLine("IF (@direction = 'DOWN' OR @direction = 'BOTH')");
					sb.AppendLine("BEGIN");
					sb.AppendLine("	SET @newItemCount = (SELECT COUNT(*) FROM #TmpIds)");
					sb.AppendLine("	SET @count = @newItemCount");
					sb.AppendLine("	SET @index = 1");
					sb.AppendLine("	WHILE(@newItemCount > 0 and @index <= @levels)");
					sb.AppendLine("	BEGIN");
					sb.Append("		INSERT INTO #TmpIds(");

					ii = 0;
					foreach (var columnRelationship in _childTable.SelfReferenceRelation.ColumnRelationships.ToList())
					{
						var parentColumn = columnRelationship.ParentColumn;
						var childColumn = columnRelationship.ChildColumn;
						sb.Append("[" + parentColumn.DatabaseName + "]");
						if (ii < _childTable.SelfReferenceRelation.FkColumns.Count() - 1) sb.Append(", ");
						ii++;
					}
					sb.AppendLine(")");

					sb.AppendLine("		SELECT");

					ii = 0;
					foreach (var columnRelationship in _childTable.SelfReferenceRelation.ColumnRelationships.ToList())
					{
						var parentColumn = columnRelationship.ParentColumn;
						var childColumn = columnRelationship.ChildColumn;
						sb.Append("	[" + parentColumn.DatabaseName + "]");
						if (ii < _childTable.SelfReferenceRelation.FkColumns.Count() - 1) sb.AppendLine(", ");
						ii++;
					}
					sb.AppendLine();

					sb.AppendLine("		FROM ");
					sb.AppendLine("			[" + _childTable.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(_model, _childTable) + "]");
					sb.AppendLine("			INNER JOIN #TmpIds ON ");

					ii = 0;
					foreach (var columnRelationship in _childTable.SelfReferenceRelation.ColumnRelationships.ToList())
					{
						var parentColumn = columnRelationship.ParentColumn;
						var childColumn = columnRelationship.ChildColumn;
						sb.AppendLine("[" + _childTable.DatabaseName + "].[" + childColumn.DatabaseName + "] = #TmpIds.[" + parentColumn.DatabaseName + "]");
						if (ii < _childTable.SelfReferenceRelation.FkColumns.Count() - 1) sb.Append(" AND ");
						ii++;
					}

					sb.AppendLine("		WHERE");

					ii = 0;
					foreach (var columnRelationship in _childTable.SelfReferenceRelation.ColumnRelationships.ToList())
					{
						var parentColumn = columnRelationship.ParentColumn;
						var childColumn = columnRelationship.ChildColumn;
						sb.AppendLine("			([" + _childTable.GetSQLSchema() + "].[" + _childTable.DatabaseName + "].[" + childColumn.DatabaseName + "] NOT IN (SELECT [" + parentColumn.DatabaseName + "] from #TmpIds))");
						if (ii < _childTable.SelfReferenceRelation.FkColumns.Count() - 1) sb.AppendLine(" AND ");
						ii++;
					}

					sb.AppendLine();
					sb.AppendLine("		SET @newItemCount = (SELECT COUNT(*) FROM #TmpIds) - @count");
					sb.AppendLine("		SET @count = (SELECT COUNT(*) FROM #TmpIds)");
					sb.AppendLine("		SET @index = @index + 1");
					sb.AppendLine("	END");
					sb.AppendLine("END");
					sb.AppendLine("IF (@direction = 'UP' OR @direction = 'BOTH')");
					sb.AppendLine("BEGIN");
					sb.AppendLine("	SET @newItemCount = (SELECT COUNT(*) FROM #TmpIds)");
					sb.AppendLine("	SET @count = @newItemCount");
					sb.AppendLine("	SET @index = 1");
					sb.AppendLine("	WHILE(@newItemCount > 0 and @index <= @levels)");
					sb.AppendLine("	BEGIN");
					sb.Append("		INSERT INTO #TmpIds(");

					ii = 0;
					foreach (var columnRelationship in _childTable.SelfReferenceRelation.ColumnRelationships.ToList())
					{
						var parentColumn = columnRelationship.ParentColumn;
						var childColumn = columnRelationship.ChildColumn;
						sb.Append("[" + parentColumn.DatabaseName + "]");
						if (ii < _childTable.SelfReferenceRelation.FkColumns.Count() - 1) sb.Append(", ");
						ii++;
					}
					sb.AppendLine(")");

					sb.AppendLine("		SELECT");

					ii = 0;
					foreach (var columnRelationship in _childTable.SelfReferenceRelation.ColumnRelationships.ToList())
					{
						var parentColumn = columnRelationship.ParentColumn;
						var childColumn = columnRelationship.ChildColumn;
						sb.Append("	[" + _childTable.DatabaseName + "].[" + parentColumn.DatabaseName + "]");
						if (ii < _childTable.SelfReferenceRelation.FkColumns.Count() - 1) sb.AppendLine(", ");
						ii++;
					}
					sb.AppendLine();

					sb.AppendLine("		FROM #TmpIds INNER JOIN [" + _childTable.DatabaseName + "] ON ");

					ii = 0;
					foreach (var columnRelationship in _childTable.SelfReferenceRelation.ColumnRelationships.ToList())
					{
						var parentColumn = columnRelationship.ParentColumn;
						var childColumn = columnRelationship.ChildColumn;
						sb.Append("			[" + _childTable.DatabaseName + "].[" + childColumn.DatabaseName + "] = #TmpIds.[" + parentColumn.DatabaseName + "]");
						if (ii < _childTable.SelfReferenceRelation.FkColumns.Count() - 1) sb.Append(" AND ");
						ii++;
					}
					sb.AppendLine();

					sb.AppendLine("		WHERE");

					ii = 0;
					foreach (var columnRelationship in _childTable.SelfReferenceRelation.ColumnRelationships.ToList())
					{
						var parentColumn = columnRelationship.ParentColumn;
						var childColumn = columnRelationship.ChildColumn;
						sb.Append("			([" + _childTable.GetSQLSchema() + "].[" + _childTable.DatabaseName + "].[" + childColumn.DatabaseName + "] NOT IN (SELECT [" + parentColumn.DatabaseName + "] from #TmpIds))");
						if (ii < _childTable.SelfReferenceRelation.FkColumns.Count() - 1) sb.AppendLine(" AND ");
						ii++;
					}
					sb.AppendLine();

					sb.AppendLine("		SET @newItemCount = (SELECT COUNT(*) FROM #TmpIds) - @count");
					sb.AppendLine("		SET @count = (SELECT COUNT(*) FROM #TmpIds)");
					sb.AppendLine("		SET @index = @index + 1");
					sb.AppendLine("	END");
					sb.AppendLine("END");
					sb.AppendLine();
					sb.AppendLine("SELECT");
					sb.Append(Globals.BuildSelectList(_childTable, _model, true));
					sb.AppendLine("FROM ");
					sb.AppendLine(_childTable.GetFullHierarchyTableJoin());
					sb.AppendLine("WHERE");

					ii = 0;
					foreach (var columnRelationship in _childTable.SelfReferenceRelation.ColumnRelationships.ToList())
					{
						var parentColumn = columnRelationship.ParentColumn;
						var childColumn = columnRelationship.ChildColumn;
						sb.AppendLine("			([" + _childTable.GetSQLSchema() + "].[" + _childTable.DatabaseName + "].[" + childColumn.DatabaseName + "] IN (SELECT [" + parentColumn.DatabaseName + "] from #TmpIds))");
						if (ii < _childTable.SelfReferenceRelation.FkColumns.Count() - 1) sb.AppendLine(" AND ");
						ii++;
					}

					sb.AppendLine();
					sb.AppendLine("END");
					sb.AppendLine("DROP TABLE #TmpIds");
				}
				else
				{
					sb.AppendLine();
					sb.AppendLine();
					sb.AppendLine("CREATE PROCEDURE " + storedProcName);
					sb.AppendLine("(");
					sb.AppendLine("	@xml xml");
					sb.AppendLine(")");
					sb.AppendLine("AS");
					sb.AppendLine();
					sb.AppendLine();
					sb.AppendLine("SELECT");
					sb.Append(Globals.BuildSelectList(_childTable, _model, true));
					sb.AppendLine("FROM ");
					sb.AppendLine(_childTable.GetFullHierarchyTableJoin());
					sb.AppendLine("WHERE");
					sb.AppendLine("	" + BuildWhereStatement(_currentRelation, parentTable));
				}
				sb.AppendLine();
				sb.AppendLine("GO");
				sb.AppendLine("SET QUOTED_IDENTIFIER OFF");
				sb.AppendLine("GO");
				sb.AppendLine("SET ANSI_NULLS ON");
				sb.AppendLine("GO");
				sb.AppendLine();
				if (!string.IsNullOrEmpty(_model.Database.GrantExecUser))
				{
					sb.AppendFormat("GRANT EXECUTE ON [" + parentTable.GetSQLSchema() + "].[{0}] TO [{1}]", GetStoredProcedureName(parentTable), _model.Database.GrantExecUser).AppendLine();
					sb.AppendLine("GO");
					sb.AppendLine();
				}

			}
			catch (Exception ex)
			{
				throw;
			}

		}

		#region string methods
		protected string BuildWhereStatement(Relation relation, Table parentTable)
		{
			try
			{
				var output = new StringBuilder();
				int ii = 0;
				foreach (var columnRelationship in relation.ColumnRelationships.ToList())
				{
					var parentColumn = columnRelationship.ParentColumn;
					var childColumn = columnRelationship.ChildColumn;

					var t = childColumn.ParentTableRef.Object as Table;
					output.Append("[" + t.GetSQLSchema() + "].[" + Globals.GetTableDatabaseName(_model, t) + "].[" + childColumn.DatabaseName + "]");
					output.Append(" IN (SELECT T.c.value('./" + parentColumn.DatabaseName + "[1]', '" + parentColumn.GetSQLDefaultType(true) + "')");
					output.AppendLine("\t\tFROM @xml.nodes('//Item') T(c))");
					if (ii < relation.ColumnRelationships.Count - 1)
						output.AppendLine(" AND ");

					ii++;
				}


				return output.ToString();
			}
			catch (Exception ex)
			{
				throw;
			}
		}

		private string GetStoredProcedureName(Table parentTable)
		{
			return _model.GetStoredProcedurePrefix() + "_" + Globals.GetPascalName(_model, _childTable) + "SelectBy" + _currentRelation.PascalRoleName + "" + parentTable.PascalName + "Pks";
		}

		#endregion

	}
}